package sparksql_fenxi.T2

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions.{col, lit}

object t2 {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder()
      .appName("test")
      .master("local[*]")
      .enableHiveSupport()
      .getOrCreate()


    spark.table("test.score")
      .where(col("s_score") < lit(60))
      .createTempView("score")


    spark.table("test.course")
      .where(col("c_name")==="数学")
      .createTempView("course")

    spark.table("test.teacher")
      .createTempView("teacher")

    spark.table("test.student")
      .createTempView("student")

    //  todo 第二题:查询课程名称为数学，且分数低于60分的学生的姓名和分数
    val r1 = spark.sql(
      """
        |select
        |b.s_name,
        |a.s_score
        |from score as a
        |join course as c
        |on c.c_id=a.c_id
        |join student as b
        |on b.s_id=a.s_id
        |""".stripMargin)

    r1.show


    spark.close()
  }

}
